package com.glink.manage.common;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.io.FileUtil;
import com.glink.common.exception.GeneralSimpleException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.util.Strings;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.lang.Nullable;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;

@Slf4j
public class PoiTools {

    /**
     * 导出excel
     *
     * @param headList excel的标题备注名称
     * @throws Exception
     */
    public static XSSFWorkbook createExcelHeader(List<String> headList) throws Exception {
        // 创建新的Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 在Excel工作簿中建一工作表，其名为缺省值
        XSSFSheet sheet = workbook.createSheet();
        // 在索引0的位置创建行（最顶端的行）
        XSSFRow row = sheet.createRow(0);
        // 设置excel头（第一行）的头名称
        for (int i = 0; i < headList.size(); i++) {

            // 在索引0的位置创建单元格（左上端）
            XSSFCell cell = row.createCell(i);
            // 定义单元格为字符串类型
            cell.setCellType(CellType.STRING);
            // 在单元格中输入一些内容
            cell.setCellValue(headList.get(i));
        }
        return workbook;
    }

    /**
     * 改行所有cell全部为空白是认为到了结束
     */
    public static boolean isLastRow(Row thisRow) {
        try {
            thisRow.getLastCellNum();
        } catch (Exception e) {
            return true;
        }
        return false;
    }

    /**
     * 判断规则：
     * 第一个单元格内容不是数字类型就认为是无效的数据
     * 强制第一个单元格必须是数字类型-NUMERIC
     * 有可能会传入最后数据之后的数据,数据为空
     */
    @SuppressWarnings("deprecation")
    public static boolean notDataRow(Row thisRow) throws GeneralSimpleException {
        Cell cell = thisRow.getCell(0);
        if (Objects.isNull(cell) || Objects.equals(cell.getCellType(), CellType.BLANK)) {
            return true;
        }
        log.info("导入excel的第一个栏位{}的类型{},类型{}", cell, cell.getCellType(), cell.getCellTypeEnum());
        if (Objects.equals(cell.getCellTypeEnum(), CellType.STRING)) {
            String value = cell.getStringCellValue();
            if (StringUtils.equals(cell.getStringCellValue(), "序号") || StringUtils.isNotBlank(value)) {
                return true;
            }
        }
        log.info("导入excel的第一个栏位需要的类型{}", CellType.NUMERIC);
        if (!Objects.equals(cell.getCellTypeEnum(), CellType.NUMERIC)) {
            return true;
        }

        return false;
    }

    /**
     * 单元格数据类型转换
     */
    @Nullable
    public static String getCellStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        String content = null;
        switch (cell.getCellTypeEnum()) {
            case _NONE:
                break;
            case ERROR:
                break;
            case NUMERIC:
                BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                content = bd.toPlainString();
                break;
            case STRING:
                content = cell.getStringCellValue();
                break;
            default:
                break;
        }
        content = content == null ? null : content.trim(); // 去前后空格
        return content;
    }


    @Nullable
    public static Integer getCellIntegerValue(Cell cell) {
        String value = getCellStringValue(cell);
        if (StringUtils.isNotBlank(value)) {
            return Integer.parseInt(value);
        }
        return null;
    }
    @Nullable
    public static Float getCellFloatValue(Cell cell) {
        String value = getCellStringValue(cell);
        if (StringUtils.isNotBlank(value)) {
            return new BigDecimal(value).floatValue();
        }
        return null;
    }
    @Nullable
    public static Double getCellDoubleValue(Cell cell) {
        String value = getCellStringValue(cell);
        if (StringUtils.isNotBlank(value)) {
            return Double.parseDouble(value);
        }
        return null;
    }

    @Nullable
    public static BigDecimal getCellBigDecimalValue(Cell cell) {
        String value = getCellStringValue(cell);
        if (StringUtils.isNotBlank(value)) {
            return new BigDecimal(value);
        }
        return null;
    }
    public static void addData(XSSFWorkbook workbook, List<String> fieldList, List<Map<String, String>> dataList) throws Exception {
        XSSFSheet sheet = workbook.getSheetAt(0);
        int base = sheet.getLastRowNum();
        //添加数据
        for (int n = 0; n < dataList.size(); n++) {
            // 在索引1的位置创建行（最顶端的行）
            XSSFRow row_value = sheet.createRow(base + n + 1);
            Map<String, String> dataMap = dataList.get(n);
            // ===============================================================
            for (int i = 0; i < fieldList.size(); i++) {

                // 在索引0的位置创建单元格（左上端）
                XSSFCell cell = row_value.createCell(i);
                // 定义单元格为字符串类型
                cell.setCellType(CellType.STRING);
                // 在单元格中输入一些内容
                Object objValue = dataMap.get(fieldList.get(i));
                cell.setCellValue(objValue == null ? "" : objValue.toString());
            }
            // ===============================================================
        }
    }

    public static XSSFWorkbook addDataWithObject(List dataList,List<String> fieldList)throws Exception{
        // 创建新的Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 在Excel工作簿中建一工作表，其名为缺省值
        XSSFSheet sheet = workbook.createSheet();
        //添加数据
        for (int rownum = 0; rownum < dataList.size(); rownum++) {
            try {
                Object excelVO = dataList.get(rownum);
                Map<String, Object> excelVOMap = BeanUtil.beanToMap(excelVO);
                if (rownum == 0) {
                    //创建表头
                    XSSFRow titleRow = sheet.createRow(rownum);
                    for (int i = 0; i < fieldList.size(); i++) {
                        String key = fieldList.get(i);
                        XSSFCell c = titleRow.createCell(i);
                        c.setCellStyle(cellStyle(workbook, true));
                        sheet.setColumnWidth(i, 30 * 200);
                        c.setCellValue(key);
                    }
                }
                XSSFRow r = sheet.createRow(rownum + 1);
                for (int cellnum = 0; cellnum < fieldList.size(); cellnum++) {
                    XSSFCell c = r.createCell(cellnum);
                    String key = fieldList.get(cellnum);
                    c.setCellValue(Objects.isNull(excelVOMap.get(key)) ? Strings.EMPTY : excelVOMap.get(key).toString());
                }
            } catch (Exception ex) {
                log.info("excel写入异常:{}", ex.getMessage(), ex);
            }
        }
        return workbook;
    }
    /**
     * 使用BeanUtil的beanToMap方式，表头根据注解@Alias控制
     *
     * @param dataList
     * @throws Exception
     */
    public static XSSFWorkbook addDataWithObject(List dataList) throws Exception {
        return addDataWithObject(dataList, false, false);
    }

    public static XSSFWorkbook addDataAndMeterHeaderWithObject(List dataList, String title) throws Exception {
        // 创建新的Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 在Excel工作簿中建一工作表，其名为缺省值
        XSSFSheet sheet = workbook.createSheet();
        int row = 0;
        int cellNum = 0;
        for (int rownum = row; rownum < dataList.size(); rownum++) {
            Object excelVO = dataList.get(rownum);
            Map<String, Object> excelVOMap = BeanUtil.beanToMap(excelVO);
            if(Objects.nonNull(excelVOMap)){
                cellNum = excelVOMap.keySet().size();
                if(cellNum > 0){
                    cellNum --;
                }
            }
        }
        // 表头
        XSSFRow xssfRow = sheet.createRow(row ++);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellNum));
        XSSFCell cell = xssfRow.createCell(0);
        cell.setCellValue(title);
        
        cell.setCellStyle(getCellStyle(workbook));
        
        //添加数据
        for (int rownum = 0; rownum < dataList.size(); rownum++) {
            try {
                Object excelVO = dataList.get(rownum);
                Map<String, Object> excelVOMap = BeanUtil.beanToMap(excelVO);
                if (rownum == 0) {
                    //创建表头
                    XSSFRow r = sheet.createRow(row);
                    Object[] keySet = excelVOMap.keySet().toArray();
                    for (int i = 0; i < keySet.length; i++) {
                        String key = keySet[i].toString();
                        XSSFCell c = r.createCell(i);
                        sheet.setColumnWidth(i, 30 * 200);
                        c.setCellValue(key);
                        c.setCellStyle(getCellStyle(workbook));
                    }
                    row ++;
                }
                XSSFRow r = sheet.createRow(rownum + row);
                Object[] valueCollection = excelVOMap.values().toArray();
                for (int cellnum = 0; cellnum < valueCollection.length; cellnum++) {
                    XSSFCell c = r.createCell(cellnum);
                    c.setCellValue(Objects.isNull(valueCollection[cellnum]) ? Strings.EMPTY : valueCollection[cellnum].toString());
                    c.setCellStyle(getCellStyle(workbook));
                }
            } catch (Exception ex) {
                log.info("excel写入异常:{}", ex.getMessage(), ex);
            }
        }
        return workbook;
    }
    
    private static CellStyle getCellStyle(XSSFWorkbook workbook){
        // 创建一个单元格样式，用于设置文本居中
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }
    
    /**
     * 使用BeanUtil的beanToMap方式，表头根据注解@Alias控制
     *
     * @param dataList
     * @param ignoreNullValue
     * @throws Exception
     */
    public static XSSFWorkbook addDataWithObject(List dataList, boolean ignoreNullValue, boolean isToUnderlineCase) throws Exception {
        // 创建新的Excel 工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 在Excel工作簿中建一工作表，其名为缺省值
        XSSFSheet sheet = workbook.createSheet();
        //添加数据
        for (int rownum = 0; rownum < dataList.size(); rownum++) {
            try {
                Object excelVO = dataList.get(rownum);
                Map<String, Object> excelVOMap = BeanUtil.beanToMap(excelVO);
                if (rownum == 0) {
                    //创建表头
                    XSSFRow r = sheet.createRow(rownum);
                    Object[] keySet = excelVOMap.keySet().toArray();
                    for (int i = 0; i < keySet.length; i++) {
                        String key = keySet[i].toString();
                        XSSFCell c = r.createCell(i);
                        sheet.setColumnWidth(i, 30 * 200);
                        c.setCellValue(key);
                    }
                }
                XSSFRow r = sheet.createRow(rownum + 1);
                Object[] valueCollection = excelVOMap.values().toArray();
                for (int cellnum = 0; cellnum < valueCollection.length; cellnum++) {
                    XSSFCell c = r.createCell(cellnum);
                    c.setCellValue(Objects.isNull(valueCollection[cellnum]) ? Strings.EMPTY : valueCollection[cellnum].toString());
                }
            } catch (Exception ex) {
                log.info("excel写入异常:{}", ex.getMessage(), ex);
            }
        }
        return workbook;
    }

    public static void write2Disk(XSSFWorkbook workbook, String filePath) throws Exception {
        FileOutputStream fos = null;
        try {
            // 新建一输出文件流
            fos = new FileOutputStream(filePath);
            // 把相应的Excel 工作簿存盘
            workbook.write(fos);
            fos.flush();
        } catch (Exception e) {
            e.printStackTrace();
            log.info("excel写入磁盘错误");
            throw new GeneralSimpleException("40063");
        } finally {
            IOUtils.closeQuietly(fos);
        }
    }

    public static void exportExcelFile(String path, String name, HttpServletResponse response) throws Exception {
        File f = null;
        OutputStream outputStream = null;
        try {
            f = new File(path);
            byte[] buffer = FileUtil.readBytes(f);
            if (null != buffer && buffer.length > 0) {
                // 清空response
                response.reset();
                // 设置response的Header
                response.setCharacterEncoding("UTF-8");
                response.setContentType("application/x-download");
                response.addHeader("content-type", "application/octet-stream;charset=UTF-8");
                response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(name, "UTF-8"));
                response.addHeader("Content-Length", String.valueOf(buffer.length));
                outputStream = response.getOutputStream();
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8");
                outputStream.write(buffer);
                outputStream.flush();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (Objects.nonNull(f)) {
                f.delete();
            }
            IOUtils.closeQuietly(outputStream);
        }
    }

    public static void exportFile(String path, String name, HttpServletResponse response) throws Exception {
        File f = null;
        OutputStream outputStream = null;
        try {
            f = new File(path);
            byte[] buffer = FileUtil.readBytes(f);
            if (null != buffer && buffer.length > 0) {
                // 清空response
                response.reset();
                // 设置response的Header
                response.setCharacterEncoding("UTF-8");
                response.addHeader("content-type", "application/octet-stream;charset=UTF-8");
                response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(name, "UTF-8"));
                response.addHeader("Content-Length", String.valueOf(buffer.length));
                outputStream = response.getOutputStream();
                response.setContentType("application/octet-stream; charset=UTF-8");
                outputStream.write(buffer);
                outputStream.flush();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(outputStream);
        }
    }

    /**
     * 判断是不是xls文件，是返回true
     *
     * @param inputStream
     * @return
     */
    public static Boolean checkIsXls(InputStream inputStream) {
        try {
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);
            HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
            log.info(wb.getAllNames().toString());
            wb.close();
        } catch (NotOLE2FileException ole2Ex) {
            log.info("判断是否是xls文件，", ole2Ex);
            return false;
        } catch (IOException | IllegalArgumentException ioException) {
            log.info("判断是否是xls文件，读取异常，", ioException);
            return false;
        }
        return true;
    }

    /**
     * 判断是不是xlsx文件，是返回true
     *
     * @param inputStream
     * @return
     */
    public static Boolean checkIsXlsx(InputStream inputStream) {
        OPCPackage pkg = null;
        try {
            pkg = OPCPackage.open(inputStream);
            XSSFWorkbook wb = new XSSFWorkbook(pkg);
            wb.close();
        } catch (InvalidFormatException e) {
            log.info("判断是不是xlsx文件1", e);
            return false;
        } catch (IllegalArgumentException illegalArgumentException) {
            log.info("判断是不是xlsx文件2", illegalArgumentException);
            return false;
        } catch (POIXMLException poixmlException) {
            log.info("判断是不是xlsx文件3", poixmlException);
            return true;
        } catch (IOException ioException) {
            log.info("判断是不是xlsx文件4", ioException);
            return false;
        }
        return true;
    }

    //标题单元格设置
    public static XSSFCellStyle cellStyle(XSSFWorkbook xssfWorkbook, boolean fontBold){
        XSSFFont font = xssfWorkbook.createFont();
        font.setFontName("宋体");
        if(fontBold) {
            font.setBold(true);
        }
        //设置字体大小
        font.setFontHeightInPoints((short) 11);

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
        //居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        //自动换行
        cellStyle.setWrapText(true);
        cellStyle.setFont(font);
        return cellStyle;
    }

    //获取excel所有行
    public static Iterator<Row> getFirstSheetRowsFromFilePath(String uploadXls) {
        Iterator<Row> rows = null;
        try (FileInputStream fis = new FileInputStream(uploadXls)) {
            if (uploadXls.endsWith(".xlsx")) {
                XSSFWorkbook wb = new XSSFWorkbook(fis);
                for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                    if (!wb.isSheetVeryHidden(i)) {
                        rows = wb.getSheetAt(i).rowIterator();
                        wb.close();
                        break;
                    }
                }
            } else if (uploadXls.endsWith(".xls")) {
                HSSFWorkbook wb = new HSSFWorkbook(fis);
                for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                    if (!wb.isSheetVeryHidden(i)) {
                        rows = wb.getSheetAt(i).rowIterator();
                        wb.close();
                    }
                }
            }
        } catch (Exception e) {
            log.info(e.getMessage());
            throw new RuntimeException("解析xls文档出错");
        }

        return rows;
    }

    //获取excel所有行
    public static Iterator<Row> getFirstSheetRowsFromFileStream(InputStream is, InputStream copyIs, String suffix) throws GeneralSimpleException, IOException {
        Iterator<Row> rows = null;
        suffix = suffix.toLowerCase();
        try{
            if(FileTools.checkFileType(is, suffix)) {
                if (suffix.equals("xlsx")) {
                    XSSFWorkbook wb = new XSSFWorkbook(copyIs);
                    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                        if (!wb.isSheetVeryHidden(i)) {
                            rows = wb.getSheetAt(i).rowIterator();
                            wb.close();
                            break;
                        }
                    }
                } else if (suffix.equals("xls")) {
                    HSSFWorkbook wb = new HSSFWorkbook(copyIs);
                    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                        if (!wb.isSheetVeryHidden(i)) {
                            rows = wb.getSheetAt(i).rowIterator();
                            wb.close();
                        }
                    }
                }
            }
        } catch (Exception e) {
            log.info(e.getMessage());
            throw new RuntimeException("解析xls文档出错");
        } 

        return rows;
    }
}